USE master;
GO

SET NOCOUNT ON;
GO

PRINT 'top aggregated IO queries';
SELECT TOP 10
	MIN(qs.query_hash) AS query_hash,
	SUM(qs.total_logical_reads)/SUM(qs.execution_count) AS avg_logical_reads,
    MIN(CAST(qp.query_plan AS nvarchar(MAX))) AS query_plan
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
GROUP BY qs.query_hash
ORDER BY avg_logical_reads DESC OPTION (RECOMPILE);

PRINT 'top aggregated CPU queries';
SELECT TOP 10
	MIN(qs.query_hash) AS query_hash,
    CAST(SUM(qs.total_worker_time)/(1000.0 * SUM(qs.execution_count)) AS DECIMAL (14, 2)) AS avg_cpu_time_ms,
	MIN(CAST(qp.query_plan AS nvarchar(MAX))) AS query_plan
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
GROUP BY query_plan_hash
ORDER BY avg_cpu_time_ms DESC OPTION (RECOMPILE);

-- get the execution plan by query_hash
SELECT 
    qp.dbid AS database_id,
    qp.query_plan,
	qs.sql_handle, 
    qs.plan_handle, 
    qs.execution_count, 
    qs.total_logical_reads, 
    qs.total_elapsed_time
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp 
WHERE qs.query_hash = <query_hash> OPTION (RECOMPILE);

PRINT 'top triggers by average elapsed time';
SELECT TOP 10
	object_id,
	database_id,
	DB_NAME(database_id) AS 'database_name',   
    OBJECT_NAME(object_id, database_id) AS 'trigger_name',
	cached_time,  
    last_execution_time,
	CAST((last_elapsed_time/1000.0) AS DECIMAL (14, 2)) AS last_elapsed_time_ms,
    execution_count,
	CAST((total_elapsed_time/1000.0) AS DECIMAL (14, 2)) AS total_elapsed_time_ms,  
	CAST((total_elapsed_time/(1000.0 * execution_count)) AS DECIMAL (14, 2)) AS avg_elapsed_time_ms
FROM sys.dm_exec_trigger_stats 
ORDER BY total_worker_time DESC OPTION (RECOMPILE);  

PRINT 'query execution statistics';
SELECT TOP 25
	-- the columns dbid, objectid and number are NULL for ad hoc and prepared batches
	DB_NAME(qt.dbid) AS database_name,
	--SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
	--((CASE statement_end_offset 
	--	WHEN -1 THEN DATALENGTH(qt.text)
	--	ELSE qs.statement_end_offset END 
	--		- qs.statement_start_offset)/2) + 1) AS statement_text,
	qs.creation_time,
	qs.execution_count,
	qs.last_execution_time,
	CAST((qs.min_elapsed_time / 1000.0) AS DECIMAL(14, 2)) AS min_elapsed_time_ms,
	CAST(((qs.total_elapsed_time / qs.execution_count) / 1000.0) AS DECIMAL(14, 2)) AS avg_elapsed_time_ms, 
	CAST((qs.max_elapsed_time / 1000.0) AS DECIMAL(14, 2)) AS max_elapsed_time_ms,
	CAST(((qs.total_worker_time / qs.execution_count) / 1000.0) AS DECIMAL(14, 2)) AS avg_worker_time_ms,
	qs.total_physical_reads / qs.execution_count AS avg_physical_reads, 
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads, 
    qs.total_logical_writes / qs.execution_count AS avg_logical_writes,
	CAST(query_plan AS nvarchar(MAX)) AS query_plan_xml,
	qp.query_plan
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qt.dbid > 4
--WHERE qs.last_execution_time > '20120101 15:00'
--WHERE qs.last_execution_time > DATEADD(hour, -1, GETDATE()) -- change hour time frame
--WHERE qs.execution_count = 1
--WHERE SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
--    ((CASE statement_end_offset 
--        WHEN -1 THEN DATALENGTH(qt.text)
--        ELSE qs.statement_end_offset END 
--            - qs.statement_start_offset)/2) + 1)
--      LIKE '%MyText%'

--ORDER BY qs.execution_count DESC      --Frequency
--ORDER BY qs.total_worker_time DESC    --CPU
--ORDER BY qs.total_elapsed_time DESC   --Duration
ORDER BY qs.total_logical_reads DESC  --Reads 
--ORDER BY qs.total_logical_writes DESC --Writes
--ORDER BY qs.total_physical_reads DESC --Physical Reads    
--ORDER BY avg_worker_time DESC         --Avg CPU
--ORDER BY avg_elapsed_time DESC        --Avg Duration     
--ORDER BY avg_logical_reads DESC       --Avg Reads
--ORDER BY avg_logical_writes DESC      --Avg Writes
--ORDER BY avg_physical_reads DESC      --Avg Physical Reads
OPTION (RECOMPILE);

PRINT 'procedure execution statistics';
SELECT TOP 25
	DB_NAME(ps.database_id) AS database_name,
	p.name AS procedure_name,
	ps.cached_time,
	ps.execution_count,
	ps.last_execution_time,
	CAST((ps.min_elapsed_time / 1000.0) AS DECIMAL(14, 2)) AS min_elapsed_time_ms,
	CAST(((ps.total_elapsed_time / ps.execution_count) / 1000.0) AS DECIMAL(14, 2)) AS avg_elapsed_time_ms,
	CAST((ps.max_elapsed_time / 1000.0) AS DECIMAL(14, 2)) AS max_elapsed_time_ms,
	CAST(((ps.total_worker_time / ps.execution_count) / 1000.0) AS DECIMAL(14, 2)) AS avg_worker_time_ms,
	ps.total_physical_reads / ps.execution_count AS avg_physical_reads, 
    ps.total_logical_reads / ps.execution_count AS avg_logical_reads, 
    ps.total_logical_writes / ps.execution_count AS avg_logical_writes,
	CAST(query_plan as nvarchar(MAX)) AS query_plan_xml,
	qp.query_plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS ps WITH (NOLOCK)
ON p.[object_id] = ps.[object_id]
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) As qp
WHERE ps.database_id > 4
--WHERE ps.last_execution_time > DATEADD(hour, -1, GETDATE()) -- change hour time frame
--WHERE ps.execution_count = 1

--ORDER BY ps.execution_count DESC      --Frequency
--ORDER BY ps.total_worker_time DESC    --CPU
--ORDER BY ps.total_elapsed_time DESC   --Duration
ORDER BY ps.total_logical_reads DESC  --Reads 
--ORDER BY ps.total_logical_writes DESC --Writes
--ORDER BY ps.total_physical_reads DESC --Physical Reads    
--ORDER BY avg_worker_time DESC         --Avg CPU
--ORDER BY avg_elapsed_time DESC        --Avg Duration     
--ORDER BY avg_logical_reads DESC       --Avg Reads
--ORDER BY avg_logical_writes DESC      --Avg Writes
--ORDER BY avg_physical_reads DESC      --Avg Physical Reads
OPTION (RECOMPILE);